﻿CREATE PROCEDURE [vantageexport].[s_PTPs] AS
DECLARE @Pattern sysname SET @Pattern = '\d\d\d\D{0,2}\d\d\d\D{0,1}\d\d\d\d'
IF 1 = 1 BEGIN
TRUNCATE TABLE vantageexport.t_PTP
INSERT INTO vantageexport.t_PTP
    (NoteTypeID, NoteActionCodeID, StatusID, NoteDate, NoteDate0, NoteDate1, Note, StatusDate, StatusUser, VantageID, LoanID,Phone, PhoneCategoryID, 
    IsTask, NoteUser, IsResolved, IsCollection,DateDue)
SELECT     TypeID, ISNULL(ActionCodeID, 201) AS NoteActionCodeID, StatusID, NoteDate, NoteDate0, NoteDate1, Notes, ISNULL(NoteDate, CreatedDate) 
                      AS StatusDate, CreatedUser, ExportID, LoanID, dbo.clr_RegEx_MatchGroup(Notes,'('+@Pattern+')',1) AS Phone, 0 AS PhoneCategoryID, 0 AS IsTask, REPLACE(CreatedUser,'Pat David','Patricia David') AS NoteUser, 1 AS IsResolved, 
                      CASE TypeID WHEN 1 THEN 1 ELSE 0 END AS IsCollection,PromiseDueDate
FROM         (
SELECT     PTP.AccountNumber, 1 AS TypeID, 1 AS ActionCodeID, dbo.fs_StateID__Notes__PromisedToPay() AS StatusID, PTP.CreatedDate AS NoteDate, 
                      dbo.Today(PTP.CreatedDate) AS NoteDate0, dbo.Time(PTP.CreatedDate) AS NoteDate1, PTP.Comments Notes, PTP.CreatedDate, PTP.CreatedUser, 
                      PTP.Target_PromiseToPayID AS ExportID, vantage.t_Bridge_20.LoanID,PTP.PromiseDueDate
FROM         vantage.t_Bridge_20 INNER JOIN
                      Vantage_In.dbo.Target_PromiseToPay AS PTP ON vantage.t_Bridge_20.VantageID = PTP.AccountNumber
WHERE     (vantage.t_Bridge_20.LoanID > 0)
) AS T
END
--RETURN
EXEC sp__Context_SecurityOff 1
EXEC sp__Context_ArchiveOff 1
EXEC sp__Context_WorkFlowOff 1
EXECUTE AS Login='AFC-HOME\SQLServer'

SELECT     Count,USERS.NoteUser, ISNULL(ADS.DisplayName,'Help Desk')DisplayName,dbo.StringMatch(USERS.NoteUser , ADS.DisplayName) Match
INTO #N_ADS
FROM         (SELECT NoteUser,COUNT(*) Count
                       FROM          vantageexport.t_Note GROUP BY NoteUser) AS USERS FULL OUTER JOIN
                      dbo.t_ADS_Login AS ADS ON dbo.StringMatch(USERS.NoteUser , ADS.DisplayName)>70
ORDER BY ADS.DisplayName

INSERT INTO dbo.t_Note(
LoanID,
Note,
StatusID,
StatusDate,
StatusUser,
DateDue,
DateDueBy,
NoteTypeID,
NoteActionCodeID,
Phone,
PhoneCategoryID,
NoteDate,
IsTask,
NoteUser,
IsResolved,
IsCollection,
VantageID
)
--OUTPUT inserted.ID,inserted.StatusID INTO @T
--SELECT LoanID,NoteDate FROM(
SELECT --TOP 10000
PTP.LoanID,
PTP.Note + CASE WHEN ADS.sAMAccountName IS NULL THEN ':'+PTP.NoteUser ELSE '' END Note,
PTP.StatusID,
PTP.StatusDate,
ISNULL(ADS.sAMAccountName,'help') AS StatusUser,
PTP.DateDue AS DateDue,
ISNULL(ADS.sAMAccountName,'help') AS DateDueBy,
PTP.NoteTypeID,
PTP.NoteActionCodeID,
PTP.Phone,
PTP.PhoneCategoryID,
PTP.NoteDate,
PTP.IsTask,
ISNULL(ADS.sAMAccountName,'help') AS NoteUser,
PTP.IsResolved,
PTP.IsCollection,
PTP.VantageID
FROM         vantageexport.t_PTP AS PTP
LEFT OUTER JOIN dbo.t_ADS_Login AS ADS ON PTP.NoteUser = ADS.DisplayName
INNER JOIN (SELECT LoanID,NoteDate,MAX(VantageID)VantageID FROM vantageexport.t_PTP GROUP BY LoanID,NoteDate
)PTP_ID ON PTP.LoanID = PTP_ID.LoanID AND PTP.VantageID = PTP_ID.VantageID
LEFT OUTER JOIN dbo.t_Note N ON N.LoanID = PTP.LoanID AND N.NoteDate = PTP.NoteDate
WHERE N.LoanID IS NULL
--)T GROUP BY LoanID,NoteDate HAVING COUNT(*)>1
--SELECT     Count,USERS.NoteUser, ADS.DisplayName,dbo.StringMatch(USERS.NoteUser , ADS.DisplayName) Match
--FROM         (SELECT NoteUser,COUNT(*) Count
--                       FROM          vantageexport.t_PTP GROUP BY NoteUser) AS USERS FULL OUTER JOIN
--                      t_ADS_Login AS ADS ON dbo.StringMatch(USERS.NoteUser , ADS.DisplayName)>70
--ORDER BY ADS.DisplayName

--SELECT     dbo.t_Note.LoanID, t_Note.NoteDate, t_Note.Note
--FROM         t_Note INNER JOIN
--                      vantageexport.t_PTP ON t_Note.LoanID = vantageexport.t_PTP.LoanID AND t_Note.NoteDate = vantageexport.t_PTP.NoteDate
REVERT
